using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using LitJson;
using UnityEngine;

namespace CGF
{
    public class LocalDB
    {
        public class Table
        {
            public string name;
            public Dictionary<string, Type> columns;
        }

        private SQLite sqlite;
        private IndexedDB indexedDB;
        private const int IndexedDBVersion = 1; //如果后续结构又改，手动加1再发布

        public LocalDB(string abName, Table[] tabkes)
        {
            if (Define.IsWebGL && !Application.isEditor)
            {
                indexedDB = new IndexedDB(abName, IndexedDBVersion);
                List<IndexedDB.Store> stores = new List<IndexedDB.Store>();
                foreach (var tabke in tabkes)
                {
                    stores.Add(new IndexedDB.Store()
                    {
                        name = tabke.name,
                        columns = tabke.columns.Keys.ToArray(),
                    });
                }
                indexedDB.CreateStoreIfNotExist(stores.ToArray());
            }
            else
            {
                sqlite = new SQLite(Application.persistentDataPath + "/" + abName + ".db");
                foreach (var tabke in tabkes)
                {
                    sqlite.CreateTableIfNotExist(tabke.name, tabke.columns);
                }
            }
        }

        public void Insert(string tableName, string[] columns, object[] values)
        {
            if (Define.IsWebGL && !Application.isEditor)
            {
                indexedDB.List_Insert(tableName, columns, values);
            }
            else
            {
                string columnsStr = "";
                string valuesStr = "";
                for (int i = 0; i < columns.Length; i++)
                {
                    if (columnsStr != "") columnsStr += ",";
                    columnsStr += columns[i];

                    if (valuesStr != "") valuesStr += ",";
                    valuesStr += values[i] is string ? $"'{values[i]}'" : values[i];
                }
                sqlite.Exec($"insert into {tableName} ({columnsStr}) values ({valuesStr})");
            }
        }

        public async CTask<JsonData[]> GetByColumn(string tableName, string column, string value, bool reverse = false)
        {
            CTask<JsonData[]> task = CTask<JsonData[]>.Create();
            List<JsonData> rows = new List<JsonData>();
            if (Define.IsWebGL && !Application.isEditor)
            {
                indexedDB.List_GetByColumn(tableName, column, value, strings =>
                {
                    foreach (var str in strings)
                    {
                        rows.Add(JsonMapper.ToObject(str));
                    }
                    if (reverse)
                    {
                        rows.Reverse();
                    }
                    task.SetResult(rows.ToArray());
                });
            }
            else
            {
                var history = sqlite.Select($"select * from {tableName} where {column}='{value}'");
                foreach (DataRow row in history.Rows)
                {
                    JsonData msg = new JsonData();
                    msg["senderId"] = row["senderId"].ToString();
                    msg["toUserId"] = row["toUserId"].ToString();
                    msg["content"] = row["content"].ToString();
                    rows.Add(msg);
                }
                if (reverse)
                {
                    rows.Reverse();
                }
                task.SetResult(rows.ToArray());
            }
            return await task;
        }

        public void DeleteByColumn(string tableName, string column, string value)
        {
            if (Define.IsWebGL && !Application.isEditor)
            {
                indexedDB.List_DeleteByColumn(tableName, column, value);
            }
            else
            {
                sqlite.Exec($"delete from {tableName} where {column}='{value}'");
            }
        }
    }
}
